﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Text;
using System.Windows.Forms;

namespace 图书管理系统
{
    class BookDao
    {
        public List<Book> findbooks(int currentPage,int PageSize,int method,String content)
        {
            List<Book> list = new List<Book>();
            SqlConnection conn = new SqlConnection();
            String sql=null;
            if(method==1)
            {
                 sql = "select * from Binfo_Table where BookName like %"+content+"% limit" + (currentPage - 1) * PageSize + "," + PageSize + "";
            }
            if(method==2)
            {
                sql = "select * from Binfo_Table where ISBN like %" + content + "% limit" + (currentPage - 1) * PageSize + "," + PageSize + "";
            }
            if(method==3)
            {
                sql = "select * from Binfo_Table where Author like %" + content + "% limit" + (currentPage - 1) * PageSize + "," + PageSize + "";
            }
            SqlCommand cmd=Dao.SqlConnect(sql, conn);
            try
            {
                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while(reader.Read())
                {
                    Book book = new Book();
                    book.ISBN1 = reader[0].ToString();
                    book.BookNum1 = reader[1].ToString();
                    book.BookName1 = reader[2].ToString();
                    book.Author1 = reader[3].ToString();
                    book.Press1 = reader[4].ToString();
                    book.Publication_time1 = (DateTime)reader[5];
                    book.BookIntro1 = reader[6].ToString();
                    book.BookLocation1 = reader[7].ToString();
                    book.BookStatus1 = reader[8].ToString();
                    book.Shelf1 = (int)reader[9];
                    list.Add(book);
                }
               
            }
            catch(Exception ex)
            {
                MessageBox.Show(ex.Message + "2");
            }
            finally
            {
                conn.Close();
            }
            return list;
        }
        public int count(int method,String content)
        {
            int num = 0;
            SqlConnection conn = new SqlConnection();
            String sql=null;
            if(method==1)
            {
                sql= "select count(*) from Binfo_Table where BookName=%"+content+"%";
            }
            if(method==2)
            {
                sql = "select count(*) from Binfo_Table where ISBN=%" + content + "%";
            }
            if (method == 3)
            {
                sql = "select count(*) from Binfo_Table where Author=%" + content + "%";
            }
            SqlCommand cmd = Dao.SqlConnect(sql, conn);
            try
            {
                conn.Open();
                num = (int)cmd.ExecuteScalar();
            }
            catch(Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return num;
        }
        public List<books> findbooklei(int method,String content)//查询书名类，返回books的list
        {
            
            List<books> books = new List<books>();
            SqlConnection conn = new SqlConnection();
            
            String sql = null;
            if(method==1)
            {
                sql = "select count(ISBN) sumbook,BookName,Author,Press,ISBN,BookIntro from Binfo_Table where BookName like '%" + content+ "%' and Shelf=0 group by BookName,Author,Press,ISBN,BookIntro";
            }
            if(method==2)
            {
                sql = "select count(ISBN) sumbook,BookName,Author,Press,ISBN,BookIntro from Binfo_Table where ISBN like '%" + content + "%'and Shelf=0 group by BookName,Author,Press,ISBN,BookIntro";
            }
            if(method==3)
            {
                sql = "select count(ISBN) sumbook,BookName,Author,Press,ISBN,BookIntro from Binfo_Table where Author like '%" + content + "%' and Shelf=0 group by BookName,Author,Press,ISBN,BookIntro";
            }
            SqlCommand cmd = Dao.SqlConnect(sql, conn);
            try
            {
                
                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                booksDao booksDao = new booksDao();
                while(reader.Read())
                {
                    books books1 = new books();
                    books1.Sumbook = (int)reader[0];
                    books1.BookName1 = reader[1].ToString();
                    books1.Author1 = reader[2].ToString();
                    books1.Kejiesum = booksDao.getkejiesum(reader[1].ToString());
                    books1.Press1 = reader[3].ToString();
                    books1.ISBN1 = reader[4].ToString();
                    books1.BookIntro1 = reader[5].ToString();
                    books1.GetBooks1 = booksDao.GetBooks(reader[4].ToString());
                    books.Add(books1);
                }
            }
            catch(Exception ex)
            {
                MessageBox.Show(ex.Message+"1");
            }
            finally
            {
                conn.Close();
            }
            return books;
        }
        public int updateStatus(String BookNum)
        {
            SqlConnection conn = new SqlConnection();
            SqlCommand cmd = new SqlCommand();
            String sql = "update Binfo_Table set BookStatus='不可借'  where BookNum='" + BookNum + "'";
            cmd = Dao.SqlConnect(sql, conn);
            conn.Open();
            if(cmd.ExecuteNonQuery()>=0)
            {
                conn.Close();
                return 1;
            }
            else
            {
                conn.Close();
                return 0;
            }
           
        }
        public int judge(String BookName, String BookNum)
        {
            SqlConnection conn = new SqlConnection();
            SqlCommand cmd = new SqlCommand();
            String sql = "select BookName from Binfo_Table where BookNum='" + BookNum + "'";
            cmd = Dao.SqlConnect(sql, conn);
            conn.Open();
            SqlDataReader reader = cmd.ExecuteReader();
            String Bookname = null;
           
            while(reader.Read())
            {
                Bookname = reader[0].ToString();
            }
            if(Bookname!=null)
            {
                if (Bookname.Trim() == BookName)
                {
                    conn.Close();
                    return 1;
                }
                else
                {
                    conn.Close();
                    return 0;
                }
            }
            else
            {
                return 3;
            }
            
            
        }
    }
}
